package com.dao.impl;

import com.dao.VoucherDao;
import com.entry.Enterprise;
import com.entry.Instituty;
import com.entry.Voucher;
import com.pojo.No;
import com.pojo.VouEntIns;
import com.pojo.VouInsEnt;
import com.pojo.VouInsEntUser;
import com.util.DBHelper;
import com.util.PageHelper;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.UUID;

public class VoucherDaoImpl implements VoucherDao {
    private DBHelper dbHelper = new DBHelper();
    Voucher voucher = new Voucher();

    @Override
    public ArrayList selectQuery(VouInsEnt vouInsEnt, PageHelper pageHelper, double Min, double Max) {
        StringBuilder sql = new StringBuilder("SELECT \n" +
                "\t\ti.receipt_day,\n" +
                "    i.name AS instituty_name, \n" +
                "    e1.name AS open_enterprise_name,\n" +
                "    e2.name AS enterprise_name,\n" +
                "    e1.social_uniform_code AS open_social_uniform_code, \n" +
                "    e2.social_uniform_code AS social_uniform_code,\n" +
                "    e1.enterprise_id AS open_enterprise_id,\n" +
                "    e2.enterprise_id AS enterprise_id,\n" +
                "    v.*\n" +
                "FROM \n" +
                "    voucher v\n" +
                "INNER JOIN \n" +
                "    instituty i ON v.instituty_id = i.instituty_id \n" +
                "LEFT JOIN \n" +
                "    enterprise e1 ON v.open_enterprise_id = e1.enterprise_id \n" +
                "left JOIN \n" +
                "    enterprise e2 ON v.enterprise_id = e2.enterprise_id \n" +
                "WHERE \n" +
                "    v.enterprise_id = ?");
                ArrayList list = new ArrayList();
                list.add(vouInsEnt.getEnterprise_id());
        if (vouInsEnt.getNo() != null) {
            sql.append("and v.no like ? ");
            list.add("%" + vouInsEnt.getNo() + "%");
        }
        if (vouInsEnt.getOpen_enterprise_name() != null) {
            sql.append("and v.open_enterprise_name = ? ");
            list.add(vouInsEnt.getOpen_enterprise_name());
        }
        if (vouInsEnt.getEnterprise_name() != null) {
            sql.append("and v.enterprise_name = ?");
            list.add(vouInsEnt.getEnterprise_name());
        }
        if (vouInsEnt.getOpen_time() != null) {
            sql.append("and v.open_time = ? ");
            list.add(vouInsEnt.getOpen_time());
        }
        if (Max != 0) {
            sql.append("and v.amount <= ? ");
            list.add(Max);
        } else if (Min != 0) {
            sql.append("and v.amount >= ? ");
            list.add(Min);
        }
        if (Min != 0 && Max != 0) {
            sql.append("and v.amount between ? and ?  ");
            list.add(Min);
            list.add(Max);
        }
        sql.append(" limit ?,? ");
        int startIndex = (pageHelper.getIndexPage() - 1) * pageHelper.getShowData();
        list.add(startIndex);//0
        list.add(pageHelper.getShowData());//4
        //System.out.println("sql语句"+sql.toString());
        return dbHelper.query(sql.toString(), VouInsEnt.class, list.toArray());
    }

    @Override
    public ArrayList selectQuery(VouInsEntUser vouInsEntUser) {
        String sql = "select i.name instituty_name, i.receipt_day, e1.name open_enterprise_name,e2.name enterprise_name, e1.social_uniform_code as open_social_uniform_code, e2.social_uniform_code as social_uniform_code,e1.enterprise_id open_enterprise_id,e2.enterprise_id enterprise_id,v.*,f.* \n" +
                "from \n" +
                "voucher v \n" +
                "inner join \n" +
                "instituty i on v.instituty_id = i.instituty_id \n" +
                "inner join \n" +
                "enterprise e1 on v.enterprise_id = e1.enterprise_id \n" +
                "inner join \n" +
                "financing f on f.enterprise_id = v.enterprise_id \n" +
                "inner join \n" +
                "enterprise e2 on v.open_enterprise_id = e2.enterprise_id \n" +
                "where v.no = ?";
        ArrayList list = dbHelper.query(sql, VouInsEntUser.class, vouInsEntUser.getNo());
        return list;
    }

    @Override
    public int selectQuerySize(VouInsEnt vouInsEnt, int Max, int Min) {
        StringBuilder sql = new StringBuilder("SELECT \n" +
                "\t\ti.receipt_day,\n" +
                "    i.name AS instituty_name, \n" +
                "    e1.name AS open_enterprise_name,\n" +
                "    e2.name AS enterprise_name,\n" +
                "    e1.social_uniform_code AS open_social_uniform_code, \n" +
                "    e2.social_uniform_code AS social_uniform_code,\n" +
                "    e1.enterprise_id AS open_enterprise_id,\n" +
                "    e2.enterprise_id AS enterprise_id,\n" +
                "    v.*\n" +
                "FROM \n" +
                "    voucher v\n" +
                "INNER JOIN \n" +
                "    instituty i ON v.instituty_id = i.instituty_id \n" +
                "LEFT JOIN \n" +
                "    enterprise e1 ON v.open_enterprise_id = e1.enterprise_id \n" +
                "left JOIN \n" +
                "    enterprise e2 ON v.enterprise_id = e2.enterprise_id \n" +
                "WHERE \n" +
                "    v.enterprise_id = ?");
        ArrayList list = new ArrayList();
        list.add(vouInsEnt.getEnterprise_id());
        if (vouInsEnt.getNo() != null) {
            sql.append("and v.no like ? ");
            list.add("%" + vouInsEnt.getNo() + "%");
        }
        if (vouInsEnt.getOpen_enterprise_name() != null) {
            sql.append("and v.open_enterprise_name = ? ");
            list.add(vouInsEnt.getOpen_enterprise_name());
        }
        if (vouInsEnt.getEnterprise_name() != null) {
            sql.append("and v.enterprise_name = ?");
            list.add(vouInsEnt.getEnterprise_name());
        }
        if (vouInsEnt.getOpen_time() != null) {
            sql.append("and v.open_time = ? ");
            list.add(vouInsEnt.getOpen_time());
        }
        if (Max != 0) {
            sql.append("and v.amount <= ? ");
            list.add(Max);
        } else if (Min != 0) {
            sql.append("and v.amount >= ? ");
            list.add(Min);
        }
        if (Min != 0 && Max != 0) {
            sql.append("and v.amount between ? and ? ");
            list.add(Min);
            list.add(Max);
        }
        //System.out.println("sql语句"+sql.toString());
        return dbHelper.query(sql.toString(),list.toArray());
    }


    @Override
    public ArrayList selectQueryByNo(String no) {
        String sql = "SELECT \n" +
                "    i.name AS instituty_name,\n" +
                "    e.name AS open_enterprise_name,\n" +
                "    e.social_uniform_code,\n" +
                "    v.open_time,\n" +
                "    v.amount,\n" +
                "    v.expiry_time,\n" +
                "    v.payment_interest_type,\n" +
                "    f.remark\n" +
                "FROM \n" +
                "    voucher v\n" +
                "INNER JOIN \n" +
                "    instituty i ON v.instituty_id = i.instituty_id\n" +
                "INNER JOIN \n" +
                "    enterprise e ON v.open_enterprise_id = e.enterprise_id\n" +
                "INNER JOIN \n" +
                "    financing f ON v.no = f.no\n" +
                "WHERE \n" +
                "    v.no = ?";

        return dbHelper.selectinfo(sql, VouEntIns.class, no);
    }

    @Override
    public int insertVoucher(Voucher voucher) {
//        System.out.println("insertVoucher 中的no是"+voucher.getNo());
//        System.out.println(voucher);
        String sql = "INSERT INTO voucher values (null,?,?,?,?,?, now(), CURRENT_DATE + INTERVAL 31 DAY,?,?,?,?,? )";
        int i = dbHelper.updateTable(sql,
                getno(),
                voucher.getAmount(),
                voucher.getEnterprise_id(),
                voucher.getOpen_enterprise_id(),
                voucher.getInstituty_id(),
                UUID(),
                voucher.getSource(),
                voucher.getStatus(),
                voucher.getSource_id(),
                voucher.getPayment_interest_type());
        return i;
    }

    @Override
    public ArrayList<Voucher> selectAllByNo(String no) {
        String sql="select * from voucher where no =?";
        ArrayList<Voucher> list =dbHelper.selectinfo(sql,Voucher.class,no);
        return list;
    }

    public String getno() {

        SimpleDateFormat slf = new SimpleDateFormat("yyyyMMdd");
        Date date = new Date();
        String day = slf.format(date);
        String sql="select MAX(no) as maxno from financing";
        ArrayList<No> max=dbHelper.query(sql,No.class);
        String no=max.get(0).getMaxno();
        String last=no.substring(no.length()-6);
        // 初始序列号
        int number = Integer.parseInt(last); // 将序列号转换为整数
        number++; // 增加序列号
        // 使用StringBuilder来构建序列号
        StringBuilder sb = new StringBuilder();
        String formattedNumber = Integer.toString(number);
        for (int i = 0; i < 6 - formattedNumber.length(); i++) {
            sb.append('0');
        }
        sb.append(formattedNumber);
        no = sb.toString();

        return ("R" + day + no);
    }


    public String UUID() {
        UUID uuid = UUID.randomUUID();
        String uid = uuid.toString();
        String uuidString = uid.toString().replace("-", "");
        String id = uuidString.substring(16);
//        System.out.println(id);
        return id;
    }

    @Override
    public int updateVou(Voucher voucher) {
        String sql2= "update voucher set status='B' where no= ? ";
        int j=dbHelper.updateTable(sql2,voucher.getNo());
        return j;
    }
}
